package com.hlkj.util;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

public class SQLFactory {

	public static Connection getConnection() throws SQLException {
		return DBPoolUtil.getConnection();
	}

	public static void closeConnection() throws SQLException {
		DBPoolUtil.close();
	}

	/**
	 * 以List对象形式返回一组数据
	 * 
	 * @param conn
	 * @param sql
	 * @param obj
	 * @return List<Map<String, Object>>
	 * @throws SQLException
	 */
	public static List<Map<String, Object>> queryList(Connection conn,
			String sql, Object... obj) {
		System.out.println("---SQL语句:" + sql);
		List<Map<String, Object>> list = new ArrayList<Map<String, Object>>();

		ResultSet rs = null;
		PreparedStatement ps = null;
		try {
			ps = conn.prepareStatement(sql);
			if (null != obj && obj.length > 0) {
				for (int i = 0; i < obj.length; i++) {
					ps.setObject(i + 1, obj[i]);
					System.out.println("---参数" + (i + 1) + ":" + obj[i]);
				}
			}
			rs = ps.executeQuery();
			System.out.println("");

			ResultSetMetaData rsmd = rs.getMetaData();
			while (rs.next()) {
				Map<String, Object> row = new HashMap<String, Object>();
				for (int i = 1; i <= rsmd.getColumnCount(); i++) {
					row.put(rsmd.getColumnLabel(i), rs.getObject(i));
				}
				list.add(row);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			try {
				if (ps != null) {
					ps.close();
				}
				if (rs != null) {
					rs.close();
				}
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}

		return list;
	}

	/**
	 * 以List对象形式返回一组数据
	 * 
	 * @param sql
	 * @param obj
	 * @return List<Map<String, Object>>
	 */
	public static List<Map<String, Object>> queryList(String sql, Object... obj) {
		System.out.println("---SQL语句:" + sql);

		List<Map<String, Object>> list = new ArrayList<Map<String, Object>>();

		Connection conn = null;
		ResultSet rs = null;
		PreparedStatement ps = null;

		try {
			conn = getConnection();
			ps = conn.prepareStatement(sql);
			if (null != obj && obj.length > 0) {
				for (int i = 0; i < obj.length; i++) {
					ps.setObject(i + 1, obj[i]);
					System.out.println("---参数" + (i + 1) + ":" + obj[i]);
				}
			}
			rs = ps.executeQuery();
			System.out.println("");

			ResultSetMetaData rsmd = rs.getMetaData();
			while (rs.next()) {
				Map<String, Object> row = new HashMap<String, Object>();
				for (int i = 1; i <= rsmd.getColumnCount(); i++) {
					row.put(rsmd.getColumnLabel(i).toLowerCase(), rs
							.getObject(i));
				}
				list.add(row);
			}

		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			try {
				if (ps != null) {
					ps.close();
				}
				if (rs != null) {
					rs.close();
				}
				if (conn != null) {
					closeConnection();
				}
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}

		return list;
	}

	/**
	 * 以List对象形式返回一组数据<模糊查询>
	 * 
	 * @param sql
	 * @param obj
	 * @return List<Map<String, Object>>
	 */
	public static List<Map<String, Object>> queryListLike(String sql,
			Object... obj) {
		System.out.println("---SQL语句:" + sql);

		List<Map<String, Object>> list = new ArrayList<Map<String, Object>>();

		Connection conn = null;
		ResultSet rs = null;
		PreparedStatement ps = null;

		try {
			conn = getConnection();
			ps = conn.prepareStatement(sql);
			if (null != obj && obj.length > 0) {
				int in = 1;
				for (int i = 0; i < obj.length; i++) {
					if (obj[i] != null && obj[i].toString() != "") {
						ps.setObject(in, obj[i]);
						System.out.println("---参数" + in + ":" + obj[i]);
						in++;
					}

				}
				in = 1;
			}
			rs = ps.executeQuery();
			System.out.println("");

			ResultSetMetaData rsmd = rs.getMetaData();
			while (rs.next()) {
				Map<String, Object> row = new HashMap<String, Object>();
				for (int i = 1; i <= rsmd.getColumnCount(); i++) {
					row.put(rsmd.getColumnLabel(i).toLowerCase(), rs
							.getObject(i));
				}
				list.add(row);
			}

		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			try {
				if (ps != null) {
					ps.close();
				}
				if (rs != null) {
					rs.close();
				}
				if (conn != null) {
					closeConnection();
				}
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}

		return list;
	}

	/**
	 * 以List对象形式返回一组数据<模糊查询>
	 * 
	 * @param conn
	 * @param sql
	 * @param obj
	 * @return List<Map<String, Object>>
	 */
	public static List<Map<String, Object>> queryListLike(Connection conn,
			String sql, Object... obj) {
		System.out.println("---SQL语句:" + sql);

		List<Map<String, Object>> list = new ArrayList<Map<String, Object>>();

		ResultSet rs = null;
		PreparedStatement ps = null;

		try {
			ps = conn.prepareStatement(sql);
			if (null != obj && obj.length > 0) {
				int in = 1;
				for (int i = 0; i < obj.length; i++) {
					if (obj[i] != null && obj[i].toString() != "") {
						ps.setObject(in, obj[i]);
						System.out.println("---参数" + in + ":" + obj[i]);
						in++;
					}

				}
				in = 1;
			}
			rs = ps.executeQuery();
			System.out.println("");

			ResultSetMetaData rsmd = rs.getMetaData();
			while (rs.next()) {
				Map<String, Object> row = new HashMap<String, Object>();
				for (int i = 1; i <= rsmd.getColumnCount(); i++) {
					row.put(rsmd.getColumnLabel(i).toLowerCase(), rs
							.getObject(i));
				}
				list.add(row);
			}

		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			try {
				if (ps != null) {
					ps.close();
				}
				if (rs != null) {
					rs.close();
				}
				if (conn != null) {
					closeConnection();
				}
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}

		return list;
	}

	/**
	 * 以Map对象形式返回一条数据
	 * 
	 * @param conn
	 * @param sql
	 * @param obj
	 * @return Map<String, Object>
	 */
	public static Map<String, Object> queryMap(Connection conn, String sql,
			Object... obj) {
		System.out.println("---SQL语句:" + sql);
		Map<String, Object> map = null;

		PreparedStatement ps = null;
		ResultSet rs = null;
		try {
			ps = conn.prepareStatement(sql);
			if (null != obj && obj.length > 0) {
				for (int i = 0; i < obj.length; i++) {
					ps.setObject(i + 1, obj[i]);
					System.out.println("---参数" + (i + 1) + ":" + obj[i]);
				}
			}
			rs = ps.executeQuery();
			System.out.println("");

			ResultSetMetaData rsmd = rs.getMetaData();
			if (rs.next()) {
				map = new HashMap<String, Object>();
				for (int i = 1; i <= rsmd.getColumnCount(); i++) {
					map.put(rsmd.getColumnLabel(i).toLowerCase(), rs
							.getObject(i));
				}
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			try {
				if (ps != null) {
					ps.close();
				}
				if (rs != null) {
					rs.close();
				}
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}

		return map;
	}

	/**
	 * 以Map对象形式返回一条数据
	 * 
	 * @param sql
	 * @param obj
	 * @return Map<String, Object>
	 */
	public static Map<String, Object> queryMap(String sql, Object... obj) {
		System.out.println("---SQL语句:" + sql);
		Map<String, Object> map = null;

		Connection conn = null;
		PreparedStatement ps = null;
		ResultSet rs = null;
		try {
			conn = getConnection();
			ps = conn.prepareStatement(sql);
			if (null != obj && obj.length > 0) {
				for (int i = 0; i < obj.length; i++) {
					ps.setObject(i + 1, obj[i]);
					System.out.println("---参数" + (i + 1) + ":" + obj[i]);
				}
			}
			rs = ps.executeQuery();
			System.out.println("");

			ResultSetMetaData rsmd = rs.getMetaData();
			if (rs.next()) {
				map = new HashMap<String, Object>();
				for (int i = 1; i <= rsmd.getColumnCount(); i++) {
					map.put(rsmd.getColumnLabel(i).toLowerCase(), rs
							.getObject(i));
				}
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			try {
				if (ps != null) {
					ps.close();
				}
				if (rs != null) {
					rs.close();
				}
				if (conn != null) {
					closeConnection();
				}
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}

		return map;
	}

	/**
	 * 获取列表的条数
	 * 
	 * @param conn
	 * @param sql
	 * @param obj
	 * @return int
	 */
	public static int queryCount(Connection conn, String sql, Object... obj) {
		int count = Integer.valueOf(SQLFactory.queryMap(conn,
				"SELECT COUNT(*) AS N FROM (" + sql + ") A", obj).get("n")
				.toString());
		return count;
	}

	/**
	 * 获取列表的条数
	 * 
	 * @param sql
	 * @param obj
	 * @return int
	 */
	public static int queryCount(String sql, Object... obj) {
		int count = Integer.valueOf(SQLFactory.queryMap(
				"SELECT COUNT(*) AS N FROM (" + sql + ") A", obj).get("n")
				.toString());
		return count;
	}

	/**
	 * 获取列表的条数<模糊查询>
	 * 
	 * @param sql
	 * @param obj
	 * @return int
	 */
	public static int queryCountLike(String sql, Object... obj) {
		sql = "SELECT COUNT(*) AS N FROM (" + sql + ") A";
		System.out.println("---SQL语句:" + sql);
		Map<String, Object> map = new HashMap<String, Object>();

		Connection conn = null;
		PreparedStatement ps = null;
		ResultSet rs = null;
		try {
			conn = getConnection();
			ps = conn.prepareStatement(sql);
			if (null != obj && obj.length > 0) {
				int in = 1;
				for (int i = 0; i < obj.length; i++) {
					if (obj[i] != null && obj[i].toString() != "") {
						ps.setObject(in, obj[i]);
						System.out.println("---参数" + in + ":" + obj[i]);
						in++;
					}
				}
				in = 1;
			}
			rs = ps.executeQuery();
			System.out.println("");

			ResultSetMetaData rsmd = rs.getMetaData();
			if (rs.next()) {
				for (int i = 1; i <= rsmd.getColumnCount(); i++) {
					map.put(rsmd.getColumnLabel(i).toLowerCase(), rs
							.getObject(i));
				}
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			try {
				if (ps != null) {
					ps.close();
				}
				if (rs != null) {
					rs.close();
				}
				if (conn != null) {
					closeConnection();
				}
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}

		return Integer.valueOf(map.get("n").toString());
	}

	/**
	 * 获取列表的条数<模糊查询>
	 * 
	 * @param conn
	 * @param sql
	 * @param obj
	 * @return int
	 */
	public static int queryCountLike(Connection conn, String sql, Object... obj) {
		sql = "SELECT COUNT(*) AS N FROM (" + sql + ") A";
		System.out.println("---SQL语句:" + sql);
		Map<String, Object> map = new HashMap<String, Object>();

		PreparedStatement ps = null;
		ResultSet rs = null;
		try {
			ps = conn.prepareStatement(sql);
			if (null != obj && obj.length > 0) {
				int in = 1;
				for (int i = 0; i < obj.length; i++) {
					if (obj[i] != null && obj[i].toString() != "") {
						ps.setObject(in, obj[i]);
						System.out.println("---参数" + in + ":" + obj[i]);
						in++;
					}
				}
				in = 1;
			}
			rs = ps.executeQuery();
			System.out.println("");

			ResultSetMetaData rsmd = rs.getMetaData();
			if (rs.next()) {
				for (int i = 1; i <= rsmd.getColumnCount(); i++) {
					map.put(rsmd.getColumnLabel(i).toLowerCase(), rs
							.getObject(i));
				}
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			try {
				if (ps != null) {
					ps.close();
				}
				if (rs != null) {
					rs.close();
				}
				if (conn != null) {
					closeConnection();
				}
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}

		return Integer.valueOf(map.get("n").toString());
	}

	/**
	 * 新增,更新,删除数据
	 * 
	 * @param conn
	 * @param sql
	 * @param obj
	 * @return 影响行数,-1为操作失败
	 */
	public static Integer update(Connection conn, String sql, Object... obj) {
		System.out.println("---SQL语句:" + sql);
		PreparedStatement ps = null;
		Integer affectRow = 0;
		try {
			ps = conn.prepareStatement(sql);
			for (int i = 0; i < obj.length; i++) {
				ps.setObject(i + 1, obj[i]);
				System.out.println("---参数" + (i + 1) + ":" + obj[i]);
			}
			affectRow = ps.executeUpdate();
			System.out.println("");
		} catch (SQLException e) {
			affectRow = -1;
			e.printStackTrace();
		} finally {
			try {
				if (ps != null) {
					ps.close();
				}
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}

		return affectRow;
	}

	/**
	 * 新增,更新,删除数据
	 * 
	 * @param sql
	 * @param obj
	 * @return 影响行数,-1为操作失败
	 */
	public static Integer update(String sql, Object... obj) {
		System.out.println("---SQL语句:" + sql);
		Connection conn = null;
		PreparedStatement ps = null;
		Integer affectRow = 0;
		try {
			conn = getConnection();
			ps = conn.prepareStatement(sql);
			for (int i = 0; i < obj.length; i++) {
				ps.setObject(i + 1, obj[i]);
				System.out.println("---参数" + (i + 1) + ":" + obj[i]);
			}
			affectRow = ps.executeUpdate();
			System.out.println("");
		} catch (SQLException e) {
			affectRow = -1;
			e.printStackTrace();
		} finally {
			try {
				if (ps != null) {
					ps.close();
				}
				if (conn != null) {
					closeConnection();
				}
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}

		return affectRow;
	}

	/**
	 * 返回一个值
	 * 
	 * @param sql
	 * @param obj
	 * @return Object
	 */
	public static Object queryObject(String sql, Object... obj) {
		System.out.println("---SQL语句:" + sql);

		Object o = null;
		Connection conn = null;
		PreparedStatement ps = null;
		ResultSet rs = null;
		try {
			conn = getConnection();
			ps = conn.prepareStatement(sql);
			if (null != obj && obj.length > 0) {
				for (int i = 0; i < obj.length; i++) {
					ps.setObject(i + 1, obj[i]);
					System.out.println("---参数" + (i + 1) + ":" + obj[i]);
				}
			}
			rs = ps.executeQuery();
			System.out.println("");

			if (rs.next()) {
				o = rs.getObject(1);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			try {
				if (ps != null) {
					ps.close();
				}
				if (rs != null) {
					rs.close();
				}
				if (conn != null) {
					closeConnection();
				}
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}

		return o;
	}

	/**
	 * 返回一个值
	 * 
	 * @param conn
	 * @param sql
	 * @param obj
	 * @return Object
	 */
	public static Object queryObject(Connection conn, String sql, Object... obj) {
		System.out.println("---SQL语句:" + sql);

		Object o = null;
		PreparedStatement ps = null;
		ResultSet rs = null;
		try {
			ps = conn.prepareStatement(sql);
			if (null != obj && obj.length > 0) {
				for (int i = 0; i < obj.length; i++) {
					ps.setObject(i + 1, obj[i]);
					System.out.println("---参数" + (i + 1) + ":" + obj[i]);
				}
			}
			rs = ps.executeQuery();
			System.out.println("");

			if (rs.next()) {
				o = rs.getObject(1);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			try {
				if (ps != null) {
					ps.close();
				}
				if (rs != null) {
					rs.close();
				}
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}

		return o;
	}
}
